package top.suven.base.util.createcode;

import top.suven.base.util.utilize.JsonUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class GenerationClassUtil {
    private static final Logger logger = LoggerFactory.getLogger(GenerationClassUtil.class);
    /** 数据库链接URL */
    private static String url = "jdbc:postgresql://10.100.0.254:7432/redfinger";
    /** 数据库用户名 */
    private static String username = "redfinger";
    /** 数据库密码 */
    private static String password = "redfinger";
    /** 驱动*/
    private static String driverName = "org.postgresql.Driver";
    /** 包名 */
    private static String packageName;
    /** 工程名 */
    private static String projectName;
    /** 表名 */
    private static String tableName;
    /** 类名 */
    private static String className;
    /** 主键 */
    private static String idKey;
    /** 注释用户名 */
    private static String createUserName = System.getProperty("user.name");

    public static Connection getConn()throws Exception{
        Connection con = null;

        try{
            Class.forName(driverName);
            System.out.println("数据库驱动成功");
            con = DriverManager.getConnection(url , username , password);
        }catch(ClassNotFoundException e){
            System.out.println(String.format("加载数据库引擎失败%s",e.getMessage()));
            throw e;
        }catch(SQLException se) {
            System.out.println(String.format("数据库连接失败！%s",se.getMessage()));
            throw se;
        }catch (Exception var3) {
            System.out.println(String.format("数据库失败！%s", var3.getMessage()));
            throw var3;
        }
        System.out.println("连接数据库动成功");
        con.setAutoCommit(false);
        return con;
    }

    public static PreparedStatement getPreparedStatement()throws Exception{
        System.out.println(tableName);
        PreparedStatement preparedStatement = getConn().prepareStatement(new StringBuffer("select * from ").append(tableName).append(" limit 1").toString());
        System.out.println("PreparedStatement:"+preparedStatement.getMaxRows());
        return preparedStatement;
    }

    private static Map getComments() throws Exception{
        Map comments = new HashMap<String, String>();
        System.out.println(driverName);
        Map data = new HashMap<String, String>();
        StringBuilder sql = new StringBuilder();
        if(driverName.indexOf("mysql")>-1) {
            sql.append("select column_name,column_comment,data_type from information_schema.columns where table_name ='").append(tableName).append("'");

            PreparedStatement pre = getConn().prepareStatement(sql.toString());
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                comments.put(rs.getObject(1), rs.getObject(2));
            }
            rs.close();
            pre.close();
        }else if(driverName.indexOf("post")>-1){
            sql.append("select a.attname as column_name,col_description(a.attrelid,a.attnum) as column_comment,format_type(a.atttypid,a.atttypmod) as data_type from ")
                .append("pg_class as c,pg_attribute as a where c.relname = '").append(tableName).append("' and a.attrelid = c.oid and a.attnum>0");

            PreparedStatement pre = getConn().prepareStatement(sql.toString());
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                comments.put(rs.getObject(1), rs.getObject(2));
            }
        }
        System.out.println(comments.toString());
        return comments;
    }

    private static String getTableComment() throws Exception{
        String returnName = "";
        StringBuilder sql = new StringBuilder();
        if(driverName.indexOf("mysql")>-1) {
            sql.append("select table_comment from information_schema.tables where table_name ='").append(tableName).append("'");
            PreparedStatement pre = getConn().prepareStatement(sql.toString());
            ResultSet rs = pre.executeQuery();

            if (rs.next()) {
                returnName = rs.getString(1);
            }
            rs.close();
            pre.close();
        }else if(driverName.indexOf("post")>-1){
            sql.append("select cast(obj_description(relfilenode,'pg_class') as varchar) as table_comment from ")
                    .append("pg_class c where  relkind = 'r' and relname not like 'pg_%' and relname = '").append(tableName).append("'");

            PreparedStatement pre = getConn().prepareStatement(sql.toString());
            ResultSet rs = pre.executeQuery();

            if (rs.next()) {
                returnName = rs.getString(1);
            }

            rs.close();
            pre.close();
        }
        return returnName;
    }

    public static void generationModel()throws Exception{

        PreparedStatement prepare = getPreparedStatement();
        ResultSet rs = prepare.executeQuery();
        System.out.println("ResultSet:"+rs.toString());
        ResultSetMetaData metaData = rs.getMetaData();
        System.out.println("ResultSetMetaData:"+metaData.getColumnCount());

        Map comments = getComments();
        StringBuilder classsb = new StringBuilder();
        StringBuilder params = new StringBuilder();
        StringBuilder methodsb = new StringBuilder();


        for (int i=1,length=metaData.getColumnCount();i<=length;i++) {
            String columnName = metaData.getColumnName(i).toLowerCase();
            int columnType = metaData.getColumnType(i);
            // 参数
            String paramUpper = relaceColumnNameUpper(columnName);
            String paramLower = relaceColumnNameLower(columnName);
            StringBuilder sb = new StringBuilder(paramLower);
            if(columnName.equals(idKey)||columnName.equals("create_time")||columnName.equals("modify_time")){
                continue;
            }
            // 参数
            String returnType = "String";
            if((columnType == Types.INTEGER && columnName.indexOf("id")!=-1)||columnType == Types.BIGINT ){
                returnType = "long";
            }else if (columnType == Types.INTEGER || columnName.endsWith("status")) {
                returnType = "int";
            }else if (columnType == Types.FLOAT || columnType == Types.REAL) {
                returnType = "float";
            } else if (columnType == Types.DOUBLE) {
                returnType = "double";
            } else if (columnType == Types.DATE || columnType == Types.TIMESTAMP || columnType == Types.TIME) {
                returnType = "Date";
            }
            String comment = (String) comments.get(columnName);
            if (StringUtils.isNotBlank(comment) && !"null".equals(comment))
                params.append("\t//").append(comment).append(" ").append("\r\n");
            params.append("\t").append("private ").append(returnType).append(" ");
            params.append(sb).append(";").append("\r\n");

            // get方法
            methodsb.append("\t").append("public ").append(returnType).append(" get").append(paramUpper).append("() {").append("\r\n");
            methodsb.append("\t").append("\t").append("return this.").append(sb).append(";").append("\r\n");
            methodsb.append("\t").append("}").append("\r\n\r\n");

            // set方法
            methodsb.append("\t").append("public ").append("void").append(" set").append(paramUpper).append("(").append(returnType).append(" ").append(sb).append(") {").append("\r\n");
            methodsb.append("\t").append("\t").append("this.").append(sb).append(" = ").append(sb).append(";").append("\r\n");
            methodsb.append("\t").append("}").append("\r\n\r\n");

        }
        Date now = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        params.append("\r\n").append("\r\n");
        classsb.append("package ").append(packageName).append(".model;").append("\r\n").append("\r\n")
                .append("import java.util.Date;").append("\r\n")
                .append("import cn.com.gc.core.anno.IDKEY;").append("\r\n")
                .append("import cn.com.gc.core.anno.TABLEKEY;").append("\r\n")
                .append("import BaseEntity;").append("\r\n");
        classsb.append("\r\n");
        String tableComment = getTableComment();
        if(StringUtils.isBlank(tableComment)){
            tableComment = tableName;
        }
        classsb.append("/**").append("\r\n");
        classsb.append(" * Created by ").append(createUserName).append(" on ").append(format.format(now)).append(".").append("\r\n");
        classsb.append(" *").append("\r\n");
        classsb.append(" * @ClassName  ").append(className).append(".java").append("\r\n");
        classsb.append(" * @Description : 构建").append(tableComment).append("的数据库表实体模型").append("\r\n");
        classsb.append(" * @Author ").append(createUserName).append("\r\n");
        format.applyPattern("yyyy/MM/dd HH:mm");
        classsb.append(" * @Date ").append(format.format(now)).append("\r\n");
        classsb.append(" * @Copyright: (c) 2018 gc by https://www.gc.com.cn/\n").append("\r\n");
        classsb.append(" * @Version V1.0.0").append("\r\n");
        classsb.append(" */").append("\r\n");
        classsb.append("@TABLEKEY(name = \"").append(tableName).append("\")").append("\r\n");
        classsb.append("@IDKEY(key = \"id\",value = \"").append(idKey).append("\")").append("\r\n");
        classsb.append("public class ").append(className).append(" extends BaseEntity").append(" {").append("\r\n").append("\r\n");
        classsb.append(params).append(methodsb).append("\r\n").append("}");

        // 写文件
        StringBuilder path = new StringBuilder(System.getProperty("user.dir").replaceAll("\\\\","/")).append("/").append(projectName)
                .append("/src/main/java/").append(packageName.replaceAll("\\.","/")).append("/model/").append(className).append(".java");
        File file = new File(path.toString());
        if (file.exists()) {// 存在
            throw new Exception(file.getName()+"文件已经存在");
        } else {
            file.createNewFile();// 不存在直接创建
        }
        FileWriter fw = new FileWriter(file);// 文件写IO
        fw.write(classsb.toString());
        fw.flush();
        fw.close();
    }

    public static String relaceColumnNameLower(String columnName){
        StringBuffer sb = new StringBuffer();
        Pattern p = Pattern.compile("_[a-z|A-Z]");
        Matcher m = p.matcher(columnName);
        while (m.find())
        {
            m.appendReplacement(sb,  m.group().toUpperCase());
        }
        m.appendTail(sb);
        return sb.toString().replaceAll("_", "");
    }

    public static String relaceColumnNameUpper(String columnName){
        StringBuffer sb = new StringBuffer();
        Pattern p = Pattern.compile("_[a-z|A-Z]");
        Matcher m = p.matcher(columnName);
        while (m.find())
        {
            m.appendReplacement(sb,  m.group().toUpperCase());
        }
        m.appendTail(sb);
        sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
        return sb.toString().replaceAll("_", "");
    }

    public static String getUrl() {
        return url;
    }

    public static void setUrl(String url) {
        GenerationClassUtil.url = url;
    }

    public static String getUsername() {
        return username;
    }

    public static void setUsername(String username) {
        GenerationClassUtil.username = username;
    }

    public static String getPassword() {
        return password;
    }

    public static void setPassword(String password) {
        GenerationClassUtil.password = password;
    }

    public static String getDriverName() {
        return driverName;
    }

    public static void setDriverName(String driverName) {
        GenerationClassUtil.driverName = driverName;
    }

    public static void createCode(GenerationClassBo generationClassBo)throws Exception {
        packageName = generationClassBo.getPackageName();
        projectName = generationClassBo.getProjectName();
        tableName = generationClassBo.getTableName();
        className = generationClassBo.getClassName();
        idKey = generationClassBo.getIdKey();
        generationModel();
    }


    public static void main(String[] agr) throws  Exception{
        String sql = "Select a.attnum,a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod) as data_type " +
                ",(select description from pg_catalog.pg_description where objoid=a.attrelid and objsubid=a.attnum) as descript " +
                "from pg_catalog.pg_attribute a " +
                "where 1=1 and a.attrelid=(select oid from pg_class where relname='rf_user' ) " +
                "and a.attnum>0 and not a.attisdropped order by a.attnum;";


        List list = getDataDictionaryInfoList("rf_user");
        System.out.println(JsonUtils.toJsonString(list));


    }




    public static List<String> getDataTableList( ){
        String  sql = "select tablename from pg_tables where schemaname='public' order by tablename desc ";

        PreparedStatement pre = null;
        ResultSet rs = null;
        try{
            pre = getConn().prepareStatement(sql);
            rs = pre.executeQuery();
            List list = new ArrayList<>();
            while (rs.next()){
                list.add(rs.getString(1));
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();

        }finally {
            try {
                rs.close();
                pre.close();
            }catch (Exception e){

            }
        }
        return null;

    }


    public static List<DataDictionaryInfo> getDataDictionaryInfoList( String tableName){
      String  sql = "select a.attnum,a.attname as column_name, col_description(a.attrelid,a.attnum) as column_comment, " +
                "    b.typname as db_type,a.atttypmod as length, a.attnotnull AS notnull from " +
                "    pg_class as c,pg_attribute as a,pg_type b where c.relname = '" + tableName +"' and a.attrelid = c.oid and b.oid = a.atttypid and a.attnum>0 ";

        PreparedStatement pre = null;
        ResultSet rs = null;
        try{
            pre = getConn().prepareStatement(sql);
            rs = pre.executeQuery();
            List list = new ArrayList<>();
            while (rs.next()){
                list.add(new DataDictionaryInfo(rs.getInt(1),rs.getString(2),
                        rs.getString(3), rs.getString(4),
                       rs.getInt(5),rs.getString(6)));
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();

        }finally {
            try {
                rs.close();
                pre.close();
            }catch (Exception e){

            }
        }
        return null;

    }


}
